package com.huacloud.qw.mysql.shell.command;

import com.huacloud.qw.mysql.shell.util.DBMysqlUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.shell.standard.ShellComponent;
import org.springframework.shell.standard.ShellMethod;
import org.springframework.shell.standard.ShellOption;
import javax.validation.constraints.NotEmpty;
/**
 * @author zhaoqiankun
 * Date on 2021/7/15  9:05
 * descriptio mysql的springshell的方法类
 */
@ShellComponent
@Slf4j
public class MysqlCommands {


    /**
     * mysql的工具类
     */
    private DBMysqlUtil DbMysqlUtil = null;


    /**
     * 连接方法
     * @param user     用户
     * @param password 密码
     * @param url   url(
     *          ip:端口//172.16.123.86:3306,172.16.123.87:3306,172.16.123.88:3306
     *         //172.16.123.18:3306
     *         //172.16.123.18:3306,172.16.123.25:3306
     *              172.16.123.86
     *              )
     */
    @ShellMethod(value = "连接数据库 直接回车(是默认值) [--user mysql_cncp] [--passwrod A1Nwag1IkJBNZI3m] [--url 172.16.123.18:3306 ]", group = "工具类连接")
    public void connect(
            @ShellOption(defaultValue = "mysql_cncp") @NotEmpty String user,
            @ShellOption(defaultValue = "kR9xrpjyD3A7yb9o") @NotEmpty String password,
            @ShellOption(defaultValue = "172.16.123.86:3306") @NotEmpty String url
    ) {
        try {
            //卡死
            //connect mysql_cncp kR9xrpjyD3A7yb9o 172.16.123.89,172.16.123.80,172.16.123.81
            //立马  用户名 密码
            // connect mysql_cncp kR9xrpjyD3A7yb9o 172.16.123.86:3306
            url = "jdbc:mysql://" + url + "/" + "?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC";
            String driverClass = "com.mysql.cj.jdbc.Driver";
            DbMysqlUtil = new DBMysqlUtil(driverClass, url, user, password);
            DbMysqlUtil.getConnection();
        } catch (Exception e) {
            e.printStackTrace();

        }

    }


    /**
     * 查询表信息先选择数据库
     * @param databseName 数据库名
     * @return 成功
     */
    @ShellMethod(value = "查询表信息先选择数据库 [--databaseName databaseName]", group = "工具类连接")
    public String selectDatabase(String databseName) {
        DbMysqlUtil.selectDataBase(databseName);
        return "成功";
    }


    /**
     * 除数据库连接,回车直接用
     * @return 成功
     */
    @ShellMethod(value = "去除数据库连接,回车直接用", group = "工具类连接")
    public String removeConnect() {
        DbMysqlUtil = null;
        return "成功";
    }



    @ShellMethod(value = "所有的增删改  [--sql sql] [--databaseName databaseName]sql外加”“", group = "增删改查工具类")
    public String crud(String sql,String databaseName) {
        log.info("执行SQL：" + sql);
        //"insert into t_student(name,age) values('zhaoliu',30);"
        //"update t_student set  age=50 where name ='lisi';"
        //"delete from  t_student where name ='zhaoliu';"
        try {
            DbMysqlUtil.createTable(sql,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }


    @ShellMethod(value = "所有的查询  [--sql sql] [--databaseName databaseName] sql外加”“", group = "增删改查工具类")
    public String allSelect(String sql,String databaseName) {
        log.info("执行SQL：" + sql);
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.allSelect(sql,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }

    @ShellMethod(value = "查询建表语句  [--tableName tableName] [--databaseName databaseName] ", group = "工具类获取表信息:要先指定数据库")
    public String selectCreateTable(String tableName,String databaseName) {
        //SELECT * FROM t_student
        log.info(tableName);
        try {
            DbMysqlUtil.createTableDdl(tableName,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }


    @ShellMethod(value = "查看数据库版本 回车查看", group = "工具类数据库信息")
    public String mysqlVersion() {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.mysqlVersion();
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }

    @ShellMethod(value = "获取表中所有列字段名称  [--tableName tableName] [--databaseName databaseName] ", group = "工具类获取表信息:要先指定数据库")
    public String getColumnNames(String tableName,String databaseName) {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.getColumnNames(tableName,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }

    @ShellMethod(value = "获取表中所有字段类型  [--tableName tableName] [--databaseName databaseName] ", group = "工具类获取表信息:要先指定数据库")
    public String getColumnTypes(String tableName,String databaseName) {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.getColumnTypes(tableName,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }

    @ShellMethod(value = "获取表中所有字段注释  [--tableName tableName] [--databaseName databaseName] ", group = "工具类获取表信息:要先指定数据库")
    public String getColumnComments(String tableName,String databaseName) {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.getColumnComments(tableName,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }

    @ShellMethod(value = "获取表中索引信息  [--tableName tableName] [--databaseName databaseName] ", group = "工具类获取表信息:要先指定数据库")
    public String checkIndex(String tableName,String databaseName) {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.getIndexInfo(tableName,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }

    @ShellMethod(value = "获取数据库相关信息，直接回车就行", group = "工具类数据库信息")
    public String getDataBaseInformations() {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.getDataBaseInformations();
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }


    @ShellMethod(value = "获得数据库中所有数据库名称  直接回车获取", group = "工具类数据库信息")
    public String getAllSchemas() {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.getAllSchemas();
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }


    @ShellMethod(value = "得表或视图中的所有列信息    [--tableName tableName] [--databaseName databaseName]", group = "工具类获取表信息:要先指定数据库")
    public String getTableColumns(String tableName,String databaseName) {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.getTableColumns(tableName,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }

    @ShellMethod(value = "获得该用户下面的所有视图   [--schemaName schemaName]", group = "工具类数据库信息")
    public String getAllViewList(String schemaName) {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.getAllViewList(schemaName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }


    @ShellMethod(value = "获得一个表的主键信息   [--schemaName schemaName] [--tableName tableName] ", group = "工具类获取表信息:要先指定数据库")
    public String getAllPrimaryKeys(String schemaName, String tableName) {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.getAllPrimaryKeys(schemaName, tableName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }



    @ShellMethod(value = "获得一个表的查看表结构   [--tableName tableName] [--databaseName databaseName]", group = "工具类获取表信息:要先指定数据库")
    public String describeTable(String tableName,String databaseName) {
        //SELECT * FROM t_student
        try {
            DbMysqlUtil.describeTable(tableName,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }

    @ShellMethod(value = "创建数据库 t_student [--databaseName databaseName] ", group = "测试数据")
    public String createDatabase(String databaseName) {
        log.info("要运行的databaseName：" + databaseName);
        try {
            DbMysqlUtil.createDatabase(databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }

    @ShellMethod(value = "创建表 t_student [--databaseName databaseName] " + "回车直接建表", group = "测试数据")
    public String createTable(String databaseName) {
        String sql =
                "CREATE TABLE `t_student`  (" +
                "  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键'," +
                "  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名字'," +
                "  `age` int(4) NULL DEFAULT NULL COMMENT '年龄'," +
                "  PRIMARY KEY (`id`) USING BTREE" +
                ") ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;";
        log.info("要运行的sql：" + sql);
        //"insert into t_student(name,age) values('zhaoliu',30);"
        //"update t_student set  age=50 where name ='lisi';"
        //"delete from  t_student where name ='zhaoliu';"
        try {
            DbMysqlUtil.createTable(sql,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                log.info("要运行的sql：" + sql);
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }





    @ShellMethod(value = "新增数据  [--sql sql] [--databaseName databaseName] 列子：\"insert into t_student(name,age) values('zhaoliu',30);\"", group = "测试数据")
    public String inserSql(String sql,String databaseName) {
        log.info("执行SQL：" + sql);
        //"insert into t_student(name,age) values('zhaoliu',30);"
        //"update t_student set  age=50 where name ='lisi';"
        //"delete from  t_student where name ='zhaoliu';"
        try {
            DbMysqlUtil.createTable(sql,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }


    @ShellMethod(value = "更改数据 [--sql sql] [--databaseName databaseName] 列子：\"update t_student set  age=50 where name ='zhaoliu';\" ", group = "测试数据")
    public String updateSql(String sql,String databaseName) {
        log.info("要更改的语句：" + sql);
        //"insert into t_student(name,age) values('zhaoliu',30);"
        //"update t_student set  age=50 where name ='lisi';"
        //"delete from  t_student where name ='zhaoliu';"
        try {
            DbMysqlUtil.createTable(sql,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }


    @ShellMethod(value = "删除数据  [--sql sql] [--databaseName databaseName] 列子：\"delete from  t_student where name ='zhaoliu';\" ", group = "测试数据")
    public String deleteSql(String sql,String databaseName) {
        log.info("要更改的语句：" + sql);
        try {
            DbMysqlUtil.createTable(sql,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }


    @ShellMethod(value = "查询表数据  [--tableName tableName] [--databaseName databaseName]  如：selectTable t_student", group = "测试数据")
    public String selectTable(String tableName,String databaseName) {
        log.info("操作的表：" + tableName);
        //SELECT * FROM t_student
        String sql = "SELECT * FROM  " + tableName +" LIMIT 0,5";
        try {
            DbMysqlUtil.allSelect(sql,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }


    @ShellMethod(value = "删除表 [--sql sql] [--databaseName databaseName]   列子：\"drop table t_student\" ", group = "测试数据")
    public String deleteTable(String sql,String databaseName) {
        log.info("要删除表语句：" + sql);
        try {
            DbMysqlUtil.createTable(sql,databaseName);
        } catch (Exception e) {
            if (DbMysqlUtil == null) {
                return "没有连接请先连接数据库 执行connect方法";
            }
            e.printStackTrace();
            log.info(e.getMessage());
        }
        return "成功";
    }

}
